package com.soosync.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.soosync.common.Log4jExtend;

public class SQLManager {
	/** Connection object */
	public static Connection conn = null;

	/** PreparedStatement object */
	public static PreparedStatement pstmt = null;

	/** ResultSet object */
	public static ResultSet rset = null;

	private static Log4jExtend LLog = new Log4jExtend("AbstractDAO");

	public SQLManager() {
	}

	public static void getConnection(String name) throws BaseException {
		conn = ConnectionManager.getConnection(name);
	}

	public static Object select(String p_connNm, String p_query,
			Object[] p_bind, Object p_return) throws BaseException {
		// SQL
		String sqlQuery = StringUtil.replaceBind(p_query, p_bind);
		LLog.debug(sqlQuery);

		try {
			if (conn == null || conn.isClosed()) {
				getConnection(p_connNm);
			}

			pstmt = conn.prepareStatement(p_query);
			pstmt.clearParameters();
			if (p_bind != null) {
				for (int i = 0; i < p_bind.length; i++) {
					if (p_bind[i] == null) {
						pstmt.setNull(i + 1, java.sql.Types.VARCHAR);
					} else {
						pstmt.setObject(i + 1, p_bind[i]);
					}
				}
			}

			long a = System.currentTimeMillis();
			rset = pstmt.executeQuery();
			long b = System.currentTimeMillis();
			LLog.info("AbstractDAO.selectProcess Query Time : " + (b - a));

			JSONObject jo = null;
			JSONArray jl = null;
			Object obj = null;

			if (p_return == null || p_return.equals("")) {
				if (rset.next()) {
					obj = rset.getObject(1);
				}
				return obj;
			} else if (p_return instanceof JSONObject) {
				jo = getValueObject(rset);
				return jo;
			} else if (p_return instanceof JSONArray) {
				long start = System.currentTimeMillis();
				 jl = getValueList(rset);
				long end = System.currentTimeMillis();
				LLog.info("AbstractDAO.selectProcess() : getValueList from ResultSet Time : "+ (end - start));
				return jl;
			} else {
				throw new LgtFException("Object Error");
			}
		} catch (SQLException sqle) {
			throw new LgtFException(sqle.getMessage(), sqle);
		} finally {
			ConnectionManager.close(conn);
		}
	}
	
	public static int execute(String p_connNm, String p_query, Object[] p_bind) throws BaseException {
		LLog.debug(StringUtil.replaceBind(p_query, p_bind));
		try {
			if (conn == null || conn.isClosed())
				getConnection(p_connNm);
			pstmt = conn.prepareStatement(p_query);
			if (p_bind != null) {
				for (int i = 0; i < p_bind.length; i++) {
					if (p_bind[i] == null) {
						pstmt.setNull(i + 1, java.sql.Types.VARCHAR);
					} else {
						pstmt.setObject(i + 1, p_bind[i]);
					}
				}
			}
			int ret = pstmt.executeUpdate();
			return ret;
		} catch (SQLException sqle) {
			if (sqle.getErrorCode() == 1) {
				throw new DuplicateKeyException("common.dataDupError", sqle);
			} else {
				throw new LgtFException(sqle.getMessage(), sqle);
			}
		} finally {
			ConnectionManager.close(conn);
		}
	}
	public static JSONObject getValueObject(ResultSet p_rset) {
		JSONObject jo = new JSONObject();
		try {
			ResultSetMetaData rsetmeta = p_rset.getMetaData();
			int colcnt = rsetmeta.getColumnCount();
			if (p_rset.next()) {
				for (int j = 1; j < colcnt + 1; j++) {
					if (p_rset.getObject(j) != null) {
						String key = rsetmeta.getColumnLabel(j).toLowerCase();
						try {
							jo.put(key, p_rset.getString(j));
						} catch (JSONException e) {
							e.printStackTrace();
						}
					}
				}
			}
		} catch (SQLException sqle) {
		}
		return jo;
	}
	public static JSONArray getValueList(ResultSet p_rset) {
		JSONArray ja = new JSONArray();
		try {
			ResultSetMetaData rsetmeta = p_rset.getMetaData();
			int colcnt = rsetmeta.getColumnCount();
			if (p_rset.next()) {
				JSONObject jo = new JSONObject();
				for (int j = 1; j < colcnt + 1; j++) {
					if (p_rset.getObject(j) != null) {
						String key = rsetmeta.getColumnLabel(j).toLowerCase();
						try {
							jo.put(key, p_rset.getString(j));
						} catch (JSONException e) {
							e.printStackTrace();
						}
					}
				}
				ja.put(jo);
			}
		} catch (SQLException sqle) {
		}
		return ja;
	}
}
